package com.chf.model;

import com.chf.Bean.UserInfoBean;
import com.chf.db.DbConn;
import com.chf.utils.MyUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MyModel {
    /**
     * 判断是否可以登录
     *
     * @param userName
     * @param userPwd
     * @return
     */
    public boolean CanLogin(String userName, String userPwd) {
        boolean can = false;
        String selectSql = "select * from userinfo where username = ? and userpwd = ?";
        QueryRunner queryRunner = new QueryRunner(DbConn.GetDBConn());
        try {
            List list = queryRunner.query(selectSql, new BeanListHandler<UserInfoBean>(UserInfoBean.class), userName, userPwd);
            if (list != null && list.size() > 0) {
                can = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return can;
    }

    /**
     * 添加 修改用户信息
     *
     * @param userInfoBean
     * @return
     */
    public static int AddUserInfo(UserInfoBean userInfoBean) {

        int i = 0;
        QueryRunner queryRunner = new QueryRunner(DbConn.GetDBConn());
        //修改用户信息
        if (userInfoBean.getUserId() != null) {
            String insertSql = "update userinfo set username=?,userpwd=?,usertel=?,userbirthday=? where userid=?";
            try {
                i = queryRunner.execute(insertSql, new Object[]{userInfoBean.getUserName(), userInfoBean.getUserPwd(), userInfoBean.getUserTel(), userInfoBean.getUserBirthday(), userInfoBean.getUserId()});
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        } else {
            //添加用户信息
            String insertSql = "insert into userinfo(username,userpwd,usertel,userbirthday,flag) values(?,?,?,?,0)";
            try {
                i = queryRunner.execute(insertSql, new Object[]{userInfoBean.getUserName(), userInfoBean.getUserPwd(), userInfoBean.getUserTel(), userInfoBean.getUserBirthday()});
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return i;
    }

    /**
     * 逻辑删除用户信息
     * @param uid
     * @return
     */
    public static int VDeleteUserInfo(String uid) {

        int i = 0;
        QueryRunner queryRunner = new QueryRunner(DbConn.GetDBConn());
        String delSql = "update userinfo set flag=1 where userid = ?";

        if(!uid.equals("")){
            try {
                i = queryRunner.execute(delSql,uid);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return i;
    }
    /**
     * 物理删除用户信息
     * @param uid
     * @return
     */
    public static int DeleteUserInfo(String uid) {

        int i = 0;
        QueryRunner queryRunner = new QueryRunner(DbConn.GetDBConn());
        String delSql = "delete from userinfo where userid = ?";

        if(!uid.equals("")){
            try {
                i = queryRunner.execute(delSql,uid);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return i;
    }
    /**
     * 根据ID查询用户信息
     *
     * @param uid
     * @return
     */
    public static UserInfoBean SeleteUserInfoById(String uid) {
        String selectSql = "select * from userinfo where userid = ? and flag = 0";
        UserInfoBean userInfoBean = null;
        QueryRunner queryRunner = new QueryRunner(DbConn.GetDBConn());
        try {
            userInfoBean = queryRunner.query(selectSql, new BeanHandler<UserInfoBean>(UserInfoBean.class), uid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return userInfoBean;
    }

    /**
     * 查询所有用户信息
     *
     * @return
     */
    public static List<UserInfoBean> SeleteAllUserInfo() {
        String selectSql = "select * from userinfo where flag = 0";
        List<UserInfoBean> lists = null;
        QueryRunner queryRunner = new QueryRunner(DbConn.GetDBConn());
        try {
            lists = queryRunner.query(selectSql, new BeanListHandler<UserInfoBean>(UserInfoBean.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return lists;
    }

    /**
     * 模糊查询用户信息
     *
     * @param userInfoBean
     * @return
     */
    public static List<UserInfoBean> SeleteUserInfo(UserInfoBean userInfoBean) {
        List<UserInfoBean> lists = new ArrayList<UserInfoBean>();
        //参数列表
        List lisparam = new ArrayList();
        //可变字符串
        StringBuffer selectSql = new StringBuffer("select * from userinfo where 1 = 1 and flag = 0");
        if (userInfoBean != null) {
            if (userInfoBean.getUserName() != "" && !userInfoBean.getUserName().equals("")) {
                selectSql.append(" and username like ? ");
                lisparam.add("%" + userInfoBean.getUserName() + "%");
            }
            if (userInfoBean.getUserTel() != "" && !userInfoBean.getUserTel().equals("")) {
                selectSql.append(" and usertel like ? ");
                lisparam.add("%" + userInfoBean.getUserTel() + "%");
            }
            if (userInfoBean.getUserBirthday() != null && !userInfoBean.getUserBirthday().equals("")) {
                selectSql.append(" and userbirthday >= ? ");
                lisparam.add(MyUtils.DateConverStr(userInfoBean.getUserBirthday(), "yyyy-MM-dd HH-mm-ss"));
            }
            if (userInfoBean.getUserBirthday2() != null && !userInfoBean.getUserBirthday2().equals("")) {
                selectSql.append(" and userbirthday <= ? ");
                lisparam.add(MyUtils.DateConverStr(userInfoBean.getUserBirthday2(), "yyyy-MM-dd HH-mm-ss"));
            }
        }
        QueryRunner queryRunner = new QueryRunner(DbConn.GetDBConn());
        try {
            lists = queryRunner.query(selectSql.toString(), new BeanListHandler<UserInfoBean>(UserInfoBean.class), lisparam.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return lists;
    }
}
